﻿using System;
using System.Text;
using System.Data.SqlClient;
using System.Collections.Generic; 
using System.Data;
using System.Linq;
using Comm.Model;
using Comm.Utility;

namespace Comm.DAL
{
	public partial class CartDAO
	{
        #region 向数据库中添加一条记录 +int Insert(Cart model)
        /// <summary>
        /// 向数据库中添加一条记录
        /// </summary>
        /// <param name="model">要添加的实体</param>
        /// <returns>插入数据的ID</returns>
        public int Insert(Cart model)
        {
            #region SQL语句
            const string sql = @"
INSERT INTO [dbo].[Cart] (
	[UserId]
)
VALUES (
	@UserId
);select @@IDENTITY";
            #endregion
            var res = SqlHelper.ExecuteScalar(sql,
					new SqlParameter("@UserId", model.UserId)					
                );
            return res == null ? 0 : Convert.ToInt32(res);
        }
        #endregion

        #region 删除一条记录 +int Delete(int id)
        public int Delete(int id)
        {
            const string sql = "DELETE FROM [dbo].[Cart] WHERE [Id] = @Id";
            return SqlHelper.ExecuteNonQuery(sql, new SqlParameter("@Id", id));
        }
        #endregion

        #region 根据主键ID更新一条记录 +int Update(Cart model)
        /// <summary>
        /// 根据主键ID更新一条记录
        /// </summary>
        /// <param name="model">更新后的实体</param>
        /// <returns>执行结果受影响行数</returns>
        public int Update(Cart model)
        {
            #region SQL语句
            const string sql = @"
UPDATE [dbo].[Cart]
SET 
	[UserId] = @UserId
WHERE [Id] = @Id";
            #endregion
            return SqlHelper.ExecuteNonQuery(sql,
					new SqlParameter("@Id", model.Id),					
					new SqlParameter("@UserId", model.UserId)					
                );
        }
        #endregion

        #region 分页查询一个集合 +IEnumerable<Cart> QueryList(int index, int size, object wheres, string orderField, bool isDesc = true)
        /// <summary>
        /// 分页查询一个集合
        /// </summary>
        /// <param name="index">页码</param>
        /// <param name="size">页大小</param>
        /// <param name="wheres">条件匿名类</param>
        /// <param name="orderField">排序字段</param>
        /// <param name="isDesc">是否降序排序</param>
        /// <returns>实体集合</returns>
        public IEnumerable<Cart> QueryList(int index, int size, object wheres, string orderField, bool isDesc = true)
        {
            var parameters = new List<SqlParameter>();
            var whereBuilder = new System.Text.StringBuilder();
            if (wheres != null)
            {
                var props = wheres.GetType().GetProperties();
                foreach (var prop in props)
                {
                    if (prop.Name.Equals("__o", StringComparison.InvariantCultureIgnoreCase))
                    {
                        // 操作符
                        whereBuilder.AppendFormat(" {0} ", prop.GetValue(wheres, null).ToString());
                    }
                    else
                    {
                        var val = prop.GetValue(wheres, null).ToString();
                        whereBuilder.AppendFormat(" [{0}] = @{0} ", prop.Name);
                        parameters.Add(new SqlParameter("@" + prop.Name, val));
                    }
                }
            }
            var sql = SqlHelper.GenerateQuerySql("Cart", new[] { "Id", "UserId" }, index, size, whereBuilder.ToString(), orderField, isDesc);
            using (var reader = SqlHelper.ExecuteReader(sql, parameters.ToArray()))
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        yield return SqlHelper.MapEntity<Cart>(reader);
                    }
                }
            }
        }
        #endregion

        #region 查询单个模型实体 +Cart QuerySingle(object wheres)
        /// <summary>
        /// 查询单个模型实体
        /// </summary>
        /// <param name="wheres">条件匿名类</param>
        /// <returns>实体</returns>
        public Cart QuerySingle(object wheres)
        {
            var list = QueryList(1, 1, wheres, null);
            return list != null && list.Any() ? list.FirstOrDefault() : null;
        }
        #endregion

        #region 查询单个模型实体 +Cart QuerySingle(int id)
        /// <summary>
        /// 查询单个模型实体
        /// </summary>
        /// <param name="id">key</param>
        /// <returns>实体</returns>
        public Cart QuerySingle(int id)
        {
            const string sql = "SELECT TOP 1 [Id], [UserId] FROM [dbo].[Cart] WHERE [Id] = @Id";
            using (var reader = SqlHelper.ExecuteReader(sql, new SqlParameter("@Id", id)))
            {
                if (reader.HasRows)
                {
                    reader.Read();
                    return SqlHelper.MapEntity<Cart>(reader);
                }
                else
                {
                    return null;
                }
            }
        }
        #endregion

        #region 查询条数 +int QueryCount(object wheres)
        /// <summary>
        /// 查询条数
        /// </summary>
        /// <param name="wheres">查询条件</param>
        /// <returns>条数</returns>
        public int QueryCount(object wheres)
        {
            var parameters = new List<SqlParameter>();
            var whereBuilder = new System.Text.StringBuilder();
            if (wheres != null)
            {
                var props = wheres.GetType().GetProperties();
                foreach (var prop in props)
                {
                    if (prop.Name.Equals("__o", StringComparison.InvariantCultureIgnoreCase))
                    {
                        // 操作符
                        whereBuilder.AppendFormat(" {0} ", prop.GetValue(wheres, null).ToString());
                    }
                    else
                    {
                        var val = prop.GetValue(wheres, null).ToString();
                        whereBuilder.AppendFormat(" [{0}] = @{0} ", prop.Name);
                        parameters.Add(new SqlParameter("@" + prop.Name, val));
                    }
                }
            }
            var sql = SqlHelper.GenerateQuerySql("Cart", new[] { "COUNT(1)" }, whereBuilder.ToString(), string.Empty);
            var res = SqlHelper.ExecuteScalar(sql, parameters.ToArray());
            return res == null ? 0 : Convert.ToInt32(res);
        }
        #endregion
		
				/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public Cart GetModel(int Id)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select Id, UserId  ");			
			strSql.Append("  from Cart ");
			strSql.Append(" where Id=@Id");
						SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
			parameters[0].Value = Id;		
			Cart model=new Cart();
			DataSet ds=SqlHelper.Query(strSql.ToString(),parameters);
			
			if(ds.Tables[0].Rows.Count>0)
			{
			if(ds.Tables[0].Rows[0]["Id"].ToString()!="")
				{
					model.Id=int.Parse(ds.Tables[0].Rows[0]["Id"].ToString());
				}
			if(ds.Tables[0].Rows[0]["UserId"].ToString()!="")
				{
					model.UserId=int.Parse(ds.Tables[0].Rows[0]["UserId"].ToString());
				}			
				return model;
			}
			else
			{
				return null;
			}
		}

        public void GetModel1(Cart model, DataRow dr)
        {

                if (dr["Id"].ToString() != "")
                {
                    model.Id = int.Parse(dr["Id"].ToString());
                }
                if (dr["UserId"].ToString() != "")
                {
                    model.UserId = int.Parse(dr["UserId"].ToString());
                }
        }
		
		/// <summary>
		/// 获得数据列表
		/// </summary>
		public DataSet GetList(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select * ");
			strSql.Append(" FROM Cart ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			return SqlHelper.Query(strSql.ToString());
		}
		
		/// <summary>
		/// 获得前几行数据
		/// </summary>
		public DataSet GetList(int Top,string strWhere,string filedOrder)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select ");
			if(Top>0)
			{
				strSql.Append(" top "+Top.ToString());
			}
			strSql.Append(" * ");
			strSql.Append(" FROM Cart ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			strSql.Append(" order by " + filedOrder);
			return SqlHelper.Query(strSql.ToString());
		}

//---------------------------------------------------------------------
        #region 1.0 - 根据 用户 id 查询 购物车
        /// <summary>
        /// 根据 用户 id 查询 购物车
        /// </summary>
        /// <param name="usrId"></param>
        /// <returns></returns>
        public Cart GetUserCar(int usrId,int IsDel)
        {
            //看数据库中是否有 当前用户的购物车，如果有，则直接返回
            Cart car = GetModelByUser(usrId, IsDel);
            Cart car1 = GetModelByUser1(usrId, IsDel);
            //如果没有，则创建并返回
            if (car == null)
            {
                if (car1 == null)
                {
                    //创建当前用户的 购物车
                    car = new Cart() { UserId = usrId };
                    //添加到数据库
                    int newCarId = Insert(car);
                    car.Id = newCarId;
                }
                else {
                    car = car1;
                }
            }
             return car;
        }
        #endregion

        //-------------------------------------------------------------

        #region 03.根据用户ID获得实体对象 +MODEL.Car GetModelByUser(int intId)
        /// <summary>
        /// 根据用户ID获得实体对象
        /// </summary>
        /// <param name="intId">id值</param>
        /// <returns>实体对象</returns>
        public Cart GetModelByUser(int usrId,int IsDel)
        {
            StringBuilder strSql = new StringBuilder("select * from Cart c");
            strSql.AppendLine(" inner join CarItems ci on c.Id = ci.carId ");
            strSql.AppendLine(" inner join Books b on ci.bookId=b.Id ");
            strSql.Append(" where c.UserId=@usrId and ci.IsDel=@IsDel");
            Cart modelCar = new Cart();
            DataTable dt = SqlHelper.ExecuteDataTable(strSql.ToString(), new SqlParameter("@usrId", usrId),new SqlParameter("@IsDel", IsDel));
            if (dt.Rows.Count > 0)
            {
                //创建购物车，并 为购物车的属性复制
                GetModel1(modelCar, dt.Rows[0]);

                //先将 购物车的 第一个产品 加入
                CarItems carItem = new CarItems();
                DAL.CarItemsDAO dalItem = new CarItemsDAO();
                //如果购物车 有多个 明细，则循环 追加 第一个 之后的明细项
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        //购物车明细项
                        carItem = new CarItems();
                        //将后面的数据 也加入 集合
                        dalItem.GetModel1(carItem, dt.Rows[i]);
                        //加入到 购物车 的 购物项目 集合中
                        modelCar.ItemList.Add(carItem);
                    }
                //返回购物车
                return modelCar;
            }
            else
            {
                return null;
            }
        }


        public Cart GetModelByUser1(int usrId, int IsDel)
        {
            StringBuilder strSql = new StringBuilder("select * from Cart");
            strSql.Append(" where UserId=@usrId");
            Cart modelCar = new Cart();
            DataTable dt = SqlHelper.ExecuteDataTable(strSql.ToString(), new SqlParameter("@usrId", usrId));
            if (dt.Rows.Count > 0)
            {
                //添加到数据库
                Cart car = new Cart() { UserId = usrId };
                //添加到数据库
                int newCarId =int.Parse(dt.Rows[0]["Id"].ToString());
                car.Id = newCarId;
                //返回购物车
                return car;
            }
            else
            {
                return null;
            }
        }
        #endregion


        #region 06.新增记录
        /// <summary>
        /// 新增记录
        /// </summary>
        /// <param name="model">数据实体对象</param>
        /// <returns>新增行的ID</returns>
        public int AddBookItem(int productId, Cart shopCar)
        {
            //新增的购物明细项 
            CarItems itemNew = null;
            //在购物车中是否 存在此明细项
            bool isExsit = false;
            //判断 购物车中 是否已经有了产品
            if (shopCar.ItemList != null) {
                foreach (CarItems item in shopCar.ItemList)
                {
                    //1.如果有，则 在原来基础上 + 1
                    if (item.bookId == productId)
                    {
                        item.count++;
                        itemNew = item;
                        isExsit = true;
                        break;
                    }
                }
            }
            //如果购物车中没有这个产品，则 新增到购物车
            if (!isExsit)
            {
                itemNew = new CarItems() { carId = shopCar.Id, bookId = productId, count = 1 };
                shopCar.ItemList.Add(itemNew);
            }
            //创建 购物车 明细表 操作对象
            DAL.CarItemsDAO dalItem = new DAL.CarItemsDAO();
            //调用数据库 更新/新增 执行
            if (isExsit)
            {
                dalItem.Update(itemNew);
            }
            else
            {
                dalItem.Insert(itemNew);
            }

            return 1;
        }
        #endregion

	}
}